Skip to main content

How To Set Up HSM Delivery Dashboard

4 minute read                                                                                                                         Advanced​

Problem: How to find the number of contacts who did not get the business initiated message due to frequency capping regulations imposed by Meta on Marketing HSMs.​

Context: Meta recently introduced something called as Frequency capping. Explaining in a simple manner, it noticed that users get a lot of Marketing messages from businesses, and to limit SPAM daily, it restricts how many Marketing messages a user can get during a rolling period of X days.

To prevent misuse and gaming the system, they haven’t told us how many messages are allowed in how many days. You can read more about this in their own words here.

Solution: To understand contacts, templates, and campaigns affected (based on dates the broadcast was initaited) by having information of errors that were encountered visialized on a lookerstudio dashboard. This will reduce the effort in repeteadly quering this infomration from the database for every braodcast made, and to make this information easily accessible to the program / operations team to work with.​

SELECT
JSON_EXTRACT_SCALAR(m.errors, '$.app') AS app,
JSON_EXTRACT_SCALAR(m.errors, '$.payload.destination') AS destination,
JSON_EXTRACT_SCALAR(m.errors, '$.payload.type') AS type,
JSON_EXTRACT_SCALAR(m.errors, '$.payload.payload.reason') AS reason,
DATE(m.sent_at) AS sent_on,
m.flow_name AS flow_name,
m.template_uuid AS template_id,
m.body AS message,
m.media_url,
m.is_hsm,
m.contact_name,
m.contact_phone
FROM `project-name.bot-number.messages` AS m
WHERE m.bsp_status = "error"
GROUP BY
app, destination, type, reason, sent_on, flow_name, template_id, message, m.media_url, m.is_hsm, m.contact_name, m.contact_phone;

  • For this query to work for your organization, reaplce the 'projet-name' with the name of your Google Cloud project within which the Big Qusery instance is set up.
  • Replace the 'bot-number' with the chatbot number being used, pre-fixed with the country code.
Screenshot 2025-05-12 at 2 36 12 PM

How to set this up​

1. Sign into Bigquery using the appropriate email account and select the options to run a query.​

  • Navigate to bigquery. Expand the phone number (aka dataset) and select Contacts table
Screenshot 2025-05-12 at 2 40 53 PM
  • Click on the “Query’ button to create an empty query
Screenshot 2025-05-12 at 2 41 14 PM
  • You will get an empty query which looks like this
Screenshot 2025-05-12 at 2 41 33 PM

  • Copy paste the query above into the query window. Replace the phrase “dataset” with the values from the empty query. Copy everything (projectname.dataset) from the ‘ symbol till the .(dot) before contacts and paste it in the 2 places it says “dataset”. Delete the empty query so that only what you pasted is visible on the screen.

  • Hit the ‘Run’ button. You will see Query Results coming once the query has run

Screenshot 2025-05-12 at 2 42 16 PM
  • Click on Save and select “Save View”
Screenshot 2025-05-12 at 2 42 42 PM
  • In the popup you see, select the correct values. Name the table “error_hsm_and_media”
Screenshot 2025-05-12 at 2 43 05 PM
  • You will now see a new table/view popping up in the list of tables in the left of the screen
Screenshot 2025-05-12 at 2 44 10 PM

Now that the data is formatted as you need it, lets move to Part 2

2. Setting up the visualization.​

Screenshot 2025-05-12 at 3 09 14 PM

  • Click on Use my own data

  • Navigate the options, to select your project. Then the data set, and then finally the view we just created. Your dashboard is ready!

Screenshot 2025-05-12 at 3 09 46 PM

  • Finish off the steps by clicking "Edit and Share"

  • This will lead you to the same preview link as shared above.

  • Navigate to the lookerstudio homepage by clicking the top left "Lookerstudio" icon, next to the "HSM and Media Errors" name of the dashboard

Screenshot 2025-05-22 at 2 02 40 PM
  • See that a new copy of the dashboard has been created.
Screenshot 2025-05-22 at 2 03 20 PM
  • Go to this copy of the dashboard, go to edit and change the name to include your org's name for future reference.
Screenshot 2025-05-22 at 2 03 48 PM

Editing and modifying further​

  • To see a list of users and numbers or any other such details, select Edit.
  • Then Select Add a chart. Select a table.
  • Drag and drop the fields contact_name and contact_phone under “Dimension”.
  • You can now extract all the affected users when apply the relevant filters by Hovering on the top right corner of the chart,
  • clicking on “more”, then Export and selecting the format you prefer!